package com.longtop.framework.orm;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.regexp.RE;

/**
 * Ibatis ORM支持，获取分页的count sql语句
 * 
 * @author 胡砥峰
 * 
 */
public class IBatisUtils {

	// SQL语法模式
	private final static String ORDER_BY_PATTERN = "ORDER\\s+BY\\s+([^\\s,\\)]+(\\s+ASC|\\s+DESC)?\\s*[,]?\\s*)+";
	private final static String ORDER_BY_SQL_PATTERN = "^(.+)[\\s|\\)]" + ORDER_BY_PATTERN + "(.*)$";

	/**
	 * 根据结定的SQL语句生成相应的统计查询SQL
	 * 
	 * @param query
	 *            查询语句
	 * @return 返回该查询对应的COUNT语句
	 */
	public static String getCountSQL(String sql) {
		String cSql = "";
		sql = replaceCR(sql);
		if (sql.toUpperCase().indexOf("GROUP BY") != -1) {
			cSql = sql;
		}

		String subQuery = subQuery(sql);

		int idx1 = sql.toUpperCase().indexOf("SELECT") + 6;
		int idx2 = sql.indexOf(subQuery);

		StringBuffer countBuff = new StringBuffer();
		boolean isNeedSubQuery = false;
		if (idx1 < idx2) {
			isNeedSubQuery = buildCountString(sql.substring(idx1, idx2), sql, countBuff);
		} else {
			isNeedSubQuery = buildCountString("", sql, countBuff);
		}

		String result = "";
		if (!cSql.equalsIgnoreCase("")) {
			result = "SELECT COUNT(*) FROM (" + cSql + ")";
		} else {
			if (!isNeedSubQuery) {
				result = "SELECT" + countBuff + subQuery;
			} else {
				result = "SELECT COUNT(*) FROM " + countBuff;
			}
		}

		Matcher sqlMatcher = Pattern.compile(ORDER_BY_SQL_PATTERN, Pattern.CASE_INSENSITIVE).matcher(sql);
		if (sqlMatcher.matches()) {
			Matcher rplMatcher = Pattern.compile(ORDER_BY_PATTERN, Pattern.CASE_INSENSITIVE).matcher(result);
			return rplMatcher.replaceAll("");
		}
		return result;
	}

	private static String replaceCR(String sql) {
		StringBuffer sqlBuff = new StringBuffer();
		char[] sqlChar = sql.toCharArray();

		int countor = 0;
		for (int i = 0; i < sqlChar.length; i++) {
			// 对于''号内的内容不清除
			if (sqlChar[i] == '\'') {
				if (countor != 0 && countor % 2 == 0) {
					countor--;
				} else {
					countor++;
				}
			}

			if (countor <= 0 && (sqlChar[i] == '\n' || ((int) sqlChar[i]) == 9)) {
				sqlBuff.append(' ');
			} else {
				sqlBuff.append(sqlChar[i]);
			}
		}
		return sqlBuff.toString();
	}

	private static String subQuery(String query) {
		RE regexp = new RE("^(.*\\))?FROM\\(?", RE.MATCH_CASEINDEPENDENT);
		String[] froms = regexp.grep(query.split(" "));
		if (froms == null || froms.length == 0)
			return query;

		int index = 0;
		if (froms[0].length() == 4) {
			index = query.indexOf(froms[0]);
		} else if (froms[0].length() <= 6) {
			index = query.indexOf(froms[0]);
			index += froms[0].toUpperCase().indexOf("FROM");
		} else {
			index = query.indexOf(froms[0]);
			index += froms[0].toUpperCase().indexOf(")FROM") + 1;
		}
		return query.substring(index);
	}

	/**
	 * 生成 COUNT SQL子字符串
	 * 
	 * @param subQuery
	 *            查询的投影语句
	 * @param fullQuery
	 *            整体的查询语句
	 * @param countBuff
	 *            返回Count语句串
	 * 
	 * @return Count的分隔Token
	 */
	private static boolean buildCountString(String subQuery, String fullQuery, StringBuffer countBuff) {
		RE regexp = new RE("[\\s|\\(]DISTINCT[\\s|\\(]", RE.MATCH_CASEINDEPENDENT);
		boolean distinct = regexp.match(subQuery);

		if (!distinct) {
			countBuff.append(" COUNT(*) ");
			return false;
		}

		char[] chars = subQuery.toCharArray();
		char stopFlag = '\0';
		int startIdx = subQuery.toUpperCase().indexOf("DISTINCT") + 8;
		while (startIdx < chars.length) {
			if (chars[startIdx] == ' ') {
				startIdx++;
				while (chars[startIdx] == ' ') {
					startIdx++;
				}
				stopFlag = ' ';
				break;
			} else if (chars[startIdx] == '(') {
				startIdx++;
				stopFlag = ')';
				break;
			}
			startIdx = subQuery.substring(startIdx).toUpperCase().indexOf("DISTINCT") + 8;
		}

		if (stopFlag == ')') {
			StringBuffer countString = new StringBuffer(" COUNT(DISTINCT ");
			for (; startIdx < chars.length; startIdx++) {
				if (chars[startIdx] == stopFlag)
					break;

				countString.append(chars[startIdx]);
			}
			countString.append(") ");

			countBuff.append(countString);
			return false;
		} else {
			countBuff.append(" (" + fullQuery + ") tmp_count");
			return true;
		}
	}

	public static void main(String[] args) {
		System.out
				.println(getCountSQL("SELECT distinct person.name as personName, person.cifno as personCifno,person.node as nodeName, person.ccbevalstatus as ccbEvalStatus, 			person.customerlevel as customerLevel, person.custype as 			custype, person.ccbexamapprstatus as ccbexamapprstatus, person.corpersonky as objectId from CUSPerson as person 	where person.ccbrmcodeky = '3232'"));

		System.out.println(getCountSQL("select * from (select a.*  from t_sys_config a union all select * from t_sys_config)"));

	}
}
